﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.IO;

namespace CarLeaseManageSystem
{
    #region 结构
    /// <summary>
    /// 保存汽车简明信息的结构
    /// </summary>
    public struct struCar
    {
        /// <summary>
        /// 车辆编号
        /// </summary>
        public string sCarId;
        /// <summary>
        /// 车型
        /// </summary>
        public string sCarName;
        /// <summary>
        /// 车牌
        /// </summary>
        public string sCarCP;
        /// <summary>
        /// 报废否
        /// </summary>
        public string sZF;
    };
    /// <summary>
    /// 简明客户信息结构
    /// </summary>
    public struct struKeHu
    {
        /// <summary>
        /// 编号
        /// </summary>
        public string sId;
        /// <summary>
        /// 客户姓名
        /// </summary>
        public string sName;
        /// <summary>
        /// 身份证号
        /// </summary>
        public string sCarId;
        /// <summary>
        /// 是否注销
        /// </summary>
        public string sZF;
    };
    /// <summary>
    /// 信息检索结构
    /// </summary>
    public struct struMsg
    {
        /// <summary>
        /// 出租及预租信息
        /// </summary>
        public string sMsg1;
        /// <summary>
        /// 违章信息
        /// </summary>
        public string sMsg2;
        /// <summary>
        /// 出租信息条数
        /// </summary>
        public int iCZ;
        /// <summary>
        /// 预租信息条数
        /// </summary>
        public int iYZ;
        /// <summary>
        /// 违章信息条数
        /// </summary>
        public int iWZ;
        /// <summary>
        /// 未处理违章条数
        /// </summary>
        public int iNoDeal;
    }
    #endregion
    class CDbCar
    {
        private string sConnStr = "";
        private OleDbConnection dbConn;
        private DataSet dataSet;
        private OleDbDataAdapter adapter;
        private OleDbDataReader dataReader;

        public CDbCar()
        {
            if (File.Exists(Application.StartupPath + "\\Files\\dbCarMana.mdb"))
            {
                string strDataBasePath = Application.StartupPath + "\\Files\\dbCarMana.mdb";
                sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strDataBasePath;
                dbConn = new OleDbConnection(sConnStr);
            }
            else
            {
                MessageBox.Show("找不到系统数据!如果您已有备份,请重启本系统后使用“文件”->“数据还原”!", "致命错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        public string CONNSTR
        {
            get { return sConnStr; }
        }

        /// <summary>
        /// 获取数据集,返回值为DataSet数据集
        /// </summary>
        /// <param name="seleStr">查询语句</param>
        /// <returns>返回DataSet数据集</returns>
        public DataSet GetDataset(string seleStr)
        {
            adapter = new OleDbDataAdapter(seleStr, dbConn);
            dataSet = new DataSet();
            adapter.Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// 保存更新
        /// </summary>
        public void Save()
        {
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
            adapter.UpdateCommand = builder.GetUpdateCommand();
            adapter.Update(dataSet.Tables[0]);
            dataSet.AcceptChanges();
        }

        /// <summary>
        /// 获取指定列的数据,返回值为List列表
        /// </summary>
        /// <param name="seleStr">查询语句</param>
        /// <param name="colName">列名</param>
        /// <returns>返回列内数据</returns>
        public List<string> getOneColumn(string seleStr, string colName)
        {
            List<string> li = new List<string>();
            OleDbCommand comm = new OleDbCommand(seleStr, dbConn);
            dbConn.Open();
            dataReader = comm.ExecuteReader();
            while (dataReader.Read())
            {
                li.Add(dataReader[colName].ToString());
            }
            dataReader.Close();
            dbConn.Close();
            return li;
        }

        /// <summary>
        /// 获取客户编号、姓名、身份证号、状态。返回List列表，各个数据间用"|"分隔
        /// </summary>        
        /// <param name="statu">客户状态：ALL所有，ZXY注销，ZXN未注销</param>
        /// <returns>返回List列表，各个数据间用"|"分隔</returns>
        public List<string> GetClient(string statu)
        {
            string order = "";
            if (statu == "ZXN")
                order = "bLogOut==False";
            else
                if (statu == "ZXY")
                    order = "bLogOut==True";
                else
                    order = "1>0";

            List<string> li = new List<string>();
            string seleStr = "select iKeHuId as 编号,sName as 姓名,sIdCard as 身份证号,bLogOut as 状态 from kehuInfo where " + order + " ";
            OleDbCommand comm = new OleDbCommand(seleStr, dbConn);
            dbConn.Open();
            dataReader = comm.ExecuteReader();
            while (dataReader.Read())
            {
                li.Add(dataReader["编号"].ToString() + "|" + dataReader["姓名"].ToString() + "|" + dataReader["身份证号"].ToString() + "|" + dataReader["状态"].ToString());
            }
            dataReader.Close();
            dbConn.Close();
            return li;
        }

        /// <summary>
        /// 获取客户租车、预租、违章信息
        /// </summary>
        /// <param name="sId">编号(客户编号、车辆编号)</param>
        /// <param name="iObject">操作对象:如果是客户则输入1,如果是车辆则输入0</param>
        /// <returns>结构型数据查询结果</returns>
        public struMsg GetMsg(string sId, int iObject)
        {
            struMsg msg = new struMsg();
            string seleChuZuStr = "";
            string seleYuZuStr = "";
            string seleWZStr = "";
            switch (iObject)
            {
                case 1:
                    //查询客户租车、预租、违章信息字符串
                    seleChuZuStr = "select (select sCType from carInfo where carInfo.iCarId=a.iCarId) as 车型,a.tEndDate as 到期," +
                        "datediff('d',a.tStartDate,a.tEndDate) as 租期,a.dRiFuJE as 日租,租期*日租 as 租金,a.bCZZF as 状态 " +
                        "from chuzuInfo a where a.iKeHuId=" + sId + " order by a.bCZZF ";
                    seleYuZuStr = "select (select b.sCType from carInfo b where b.iCarId=a.iCarId) as 车型,a.dYuFuJE as 预付,a.tYZDate as 预租日期 " +
                        "from yuzuInfo a where a.iKeHuId=" + sId;
                    seleWZStr = "select (select sCType from carInfo c where c.iCarId=a.iCarId) as 车型,a.sWZAddr as 地点," +
                        "a.sWZDetail as 内容,a.dWZJE as 罚款,a.tWZDate as 时间,a.bWZZF as 处理 from weizhangInfo as a where a.iKeHuId=" + sId + " order by a.bWZZF";
                    break;
                case 0:
                    //查询车辆租车、预租、违章信息字符串
                    seleChuZuStr = "select (select sName from kehuInfo where kehuInfo.iKeHuId=a.iKeHuId) as 客户,a.tEndDate as 到期," +
                     "datediff('d',a.tStartDate,a.tEndDate) as 租期,a.dRiFuJE as 日租,租期*日租 as 租金,a.bCZZF as 状态 " +
                     "from chuzuInfo a where a.iCarId=" + sId + " order by a.bCZZF ";
                    seleYuZuStr = "select (select sName from kehuInfo where kehuInfo.iKeHuId=a.iKeHuId) as 客户,a.dYuFuJE as 预付," +
                     "a.tYZDate as 预租日期 from yuzuInfo a where a.iCarId=" + sId;
                    seleWZStr = "select (select sName from kehuInfo where kehuInfo.iKeHuId=a.iKeHuId) as 客户,a.sWZAddr as 地点," +
                     "a.sWZDetail as 内容,a.dWZJE as 罚款,a.tWZDate as 时间,a.bWZZF as 处理 from weizhangInfo as a where a.iCarId=" + sId + " order by a.bWZZF";
                    break;
            }
            //开始查询【出租】信息并构造信息字符串
            OleDbCommand comm = new OleDbCommand(seleChuZuStr, dbConn);
            dbConn.Open();
            dataReader = comm.ExecuteReader();
            if (dataReader.HasRows)
                msg.sMsg1 += "出租信息>>\r\n";
            while (dataReader.Read())
            {
                try
                {
                    msg.sMsg1 += "车型:" + dataReader["车型"].ToString() + "\t到期:" + dataReader["到期"].ToString() + "\r\n";
                }
                catch { msg.sMsg1 += "客户:" + dataReader["客户"].ToString() + "\t到期:" + dataReader["到期"].ToString() + "\r\n"; }
                msg.sMsg1 += "租期:" + dataReader["租期"].ToString();
                msg.sMsg1 += "天\t日租:" + dataReader["日租"].ToString() + "元\t租金:" + dataReader["租金"].ToString();
                msg.sMsg1 += "元\t状态:" + ((bool)dataReader["状态"] ? "已还车" : "使用中") + "\r\n\r\n";
                msg.iCZ++;
            }
            //开始查询【预租】信息并构造信息字符串
            comm = new OleDbCommand(seleYuZuStr, dbConn);
            dataReader = comm.ExecuteReader();
            if (dataReader.HasRows)
                msg.sMsg1 += "预租信息>>\r\n";
            while (dataReader.Read())
            {
                try
                {
                    msg.sMsg1 += "车型:" + dataReader["车型"].ToString() + "\r\n";
                }
                catch { msg.sMsg1 += "客户:" + dataReader["客户"].ToString() + "\r\n"; }
                msg.sMsg1 += "预付:" + dataReader["预付"].ToString() + "元\t预租日期:" + ((DateTime)dataReader["预租日期"]).Date + "\r\n";
                msg.iYZ++;
            }
            msg.sMsg1 = msg.sMsg1 == null ? "(该客户目前尚无租车记录)" : msg.sMsg1;
            //开始查询【违章】信息并构造信息字符串
            comm = new OleDbCommand(seleWZStr, dbConn);
            dataReader = comm.ExecuteReader();
            if (dataReader.HasRows)
                msg.sMsg2 += "违章信息>>\r\n";
            while (dataReader.Read())
            {
                try
                {
                    msg.sMsg2 += "车型:" + dataReader["车型"].ToString() + "\r\n";
                }
                catch { msg.sMsg2 += "客户:" + dataReader["客户"].ToString() + "\r\n"; }
                msg.sMsg2 += "内容:" + dataReader["地点"].ToString() + dataReader["内容"].ToString() + "\r\n";
                msg.sMsg2 += "罚款:" + dataReader["罚款"].ToString() + "元\t时间:" + dataReader["时间"].ToString();
                msg.sMsg2 += "\t处理:" + ((bool)dataReader["处理"] ? "已处理" : "待处理") + "\r\n\r\n";
                if (!(bool)dataReader["处理"])
                    msg.iNoDeal++;
                msg.iWZ++;
            }
            msg.sMsg2 = msg.sMsg2 == null ? "(该客户目前尚无违章记录)" : msg.sMsg2;
            dataReader.Close();
            dbConn.Close();
            return msg;
        }

        /// <summary>
        /// 为列表加载客户简明信息
        /// </summary>
        /// <returns></returns>
        public List<struKeHu> LoadList_KeHuInfo()
        {
            List<struKeHu> li = new List<struKeHu>();
            struKeHu kehu;
            string strSeleAllKeHu = "select iKeHuId as 编号,sName as 姓名,sIdCard as 身份证号,bLogOut as 注销否 from kehuInfo";
            OleDbCommand comm = new OleDbCommand(strSeleAllKeHu, dbConn);
            dbConn.Open();
            dataReader = comm.ExecuteReader();
            while (dataReader.Read())
            {
                kehu = new struKeHu();
                kehu.sId = dataReader["编号"].ToString();
                kehu.sName = dataReader["姓名"].ToString();
                kehu.sCarId = dataReader["身份证号"].ToString();
                kehu.sZF = dataReader["注销否"].ToString();
                li.Add(kehu);
            }
            dataReader.Close();
            dbConn.Close();
            return li;
        }

        /// <summary>
        /// 为列表加载车辆简明信息
        /// </summary>
        /// <returns></returns>
        public List<struCar> LoadList_Car()
        {
            List<struCar> li = new List<struCar>();
            struCar car;
            string strSeleAllCar = "select iCarId as 编号,sCType as 车型,sCPId as 车牌,bBF as 是否报废 from carInfo";
            OleDbCommand comm = new OleDbCommand(strSeleAllCar, dbConn);
            dbConn.Open();
            dataReader = comm.ExecuteReader();
            while (dataReader.Read())
            {
                car = new struCar();
                car.sCarId = dataReader["编号"].ToString(); car.sCarName = dataReader["车型"].ToString();
                car.sCarCP = dataReader["车牌"].ToString(); car.sZF = dataReader["是否报废"].ToString();
                li.Add(car);
            }
            dataReader.Close();
            dbConn.Close();
            return li;
        }

        /// <summary>
        /// 获取所有出租资料
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllChuZuInfo()
        {
            DataTable table = new DataTable();
            CDbCar car = new CDbCar();
            string expression = "select a.iCZDId as 租单号,a.iCarId as 车编号,b.sCType as 车型,b.mcphoto as 车照,a.iKeHuId as 客户号," +
                "c.sName as 客户,a.dRiFuJE as 日租,a.dYuFuJE as 预付,a.dYaJing as 押金,a.dshifuje as 实付,a.tStartDate as 开始,a.tEndDate as 结束," +
                "datediff('d',a.tStartDate,a.tEndDate) as 租期,租期*日租 as 租金,(select top 1 d.tnewdate from xuzuinfo d where d.iczdid=a.iczdid order by d.tnewdate desc) as 续期," +
                "a.bXZ as 续否,a.bCZZF as 作废,a.dYouHuiJE as 优惠 " +
                "from chuzuInfo a,carinfo b,kehuinfo c where a.ikehuid=c.ikehuid and a.icarid=b.icarid";
            table = car.GetDataset(expression).Tables[0];
            return table;
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="seleStr">查询语句</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL(string seleStr)
        {
            OleDbCommand comm = new OleDbCommand(seleStr, dbConn);
            dbConn.Open();
            int i = comm.ExecuteNonQuery();
            dbConn.Close();
            return i;
        }

        /// <summary>
        /// 执行指定查询
        /// </summary>
        /// <param name="seleStr">sql查询语句</param>
        /// <returns>返回OleDbDataReader</returns>
        public OleDbDataReader Select(string seleStr)
        {
            try
            {
                OleDbCommand comm = new OleDbCommand(seleStr, dbConn);
                dbConn.Open();
                dataReader = comm.ExecuteReader(CommandBehavior.CloseConnection);
                return dataReader;
            }
            catch { dbConn.Close(); return null; }
        }
    }
}
